import json
import sqlite3
import os

# 创建SQLite数据库并建立表
def initialize_database():
    db_path = os.path.join(os.path.dirname(__file__), '..', 'books.db')
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            isbn TEXT,
            author TEXT,
            translator TEXT,
            publisher TEXT
        )
    ''')
    conn.commit()
    conn.close()

# 将JSON数据存储到数据库
def store_books(json_data):
    db_path = os.path.join(os.path.dirname(__file__), '..', 'books.db')
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    books = json.loads(json_data)
    for book in books:
        cursor.execute('''
            SELECT COUNT(*) FROM books WHERE isbn = ?
        ''', (book.get('isbn'),))
        if cursor.fetchone()[0] == 0:  # If no matching record exists
            cursor.execute('''
                INSERT INTO books (title, isbn, author, translator, publisher)
                VALUES (?, ?, ?, ?, ?)
            ''', (book.get('title'), book.get('isbn'), book.get('author'), book.get('translator'), book.get('publisher')))
    conn.commit()
    conn.close()

# 打印所有存储在数据库中的书籍信息
def print_all_books():
    db_path = os.path.join(os.path.dirname(__file__), '..', 'books.db')
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM books')
    books = cursor.fetchall()
    conn.close()
    
    print("Books in the database:")
    for book in books:
        print(f"ID: {book[0]}, Title: {book[1]}, ISBN: {book[2]}, Author: {book[3]}, Translator: {book[4]}, Publisher: {book[5]}")

# 通过书籍名查询信息
def search_books_by_title(title):
    db_path = os.path.join(os.path.dirname(__file__), '..', 'books.db')
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM books WHERE title LIKE ?', ('%' + title + '%',))
    books = cursor.fetchall()
    conn.close()
    
    if books:
        print(f"Books matching '{title}':")
        for book in books:
            print(f"ID: {book[0]}, Title: {book[1]}, ISBN: {book[2]}, Author: {book[3]}, Translator: {book[4]}, Publisher: {book[5]}")
    else:
        print(f"No books found matching '{title}'.")

# 通过ISBN号查询信息
def search_books_by_isbn(isbn):
    db_path = os.path.join(os.path.dirname(__file__), '..', 'books.db')
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM books WHERE isbn = ?', (isbn,))
    books = cursor.fetchall()
    conn.close()
    
    if books:
        print(f"Books matching ISBN '{isbn}':")
        for book in books:
            print(f"ID: {book[0]}, Title: {book[1]}, ISBN: {book[2]}, Author: {book[3]}, Translator: {book[4]}, Publisher: {book[5]}")
    else:
        print(f"No books found matching ISBN '{isbn}'.")

# 通用查询函数，通过字段和值查询书籍
def search_books(field, value):
    db_path = os.path.join(os.path.dirname(__file__), '..', 'books.db')
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    query = f"SELECT * FROM books WHERE {field} LIKE ?"
    cursor.execute(query, ('%' + value + '%',))
    books = cursor.fetchall()
    conn.close()
    
    results = []
    for book in books:
        results.append({
            "id": book[0],
            "title": book[1],
            "isbn": book[2],
            "author": book[3],
            "translator": book[4],
            "publisher": book[5]
        })
    return results

# 示例JSON数据
sample_json = '''
[
    {
        "title": "For test server",
        "isbn": "1234567890",
        "author": "Gao wenjuan",
        "translator": "Translator Name",
        "publisher": "Publisher Name"
    },
    {
        "title": "For test client",
        "isbn": "0987654321",
        "author": "Shen jiajia",
        "translator": "Another Translator",
        "publisher": "Another Publisher"
    }
]
'''

# 主函数
def main():
    initialize_database()
    store_books(sample_json)
    print_all_books()
    print("\nSearch results:")
    search_books_by_title("Book Title")
    print("\nSearch by ISBN:")
    search_books_by_isbn("1234567890")
    print("\nGeneric search (author):")
    results = search_books("author", "Author Name")
    print(json.dumps(results, indent=4, ensure_ascii=False))  # Return results to client

if __name__ == "__main__":
    main()